package com.picvik.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import java.util.ArrayList;

import com.picvik.util.MyLog;

public class TagUtil {
	// DB related private variables
	private Connection connection = null;
	private PreparedStatement preparedStatement = null;
	private ResultSet resultSet = null;
	
	// Public Utility Methods
	
	// Insertion Methods
	public int insertNewTag(String tagname){
		
		int recentID = -1;
		// Check if tDag already exist with same tag name, if not insert it.
		try {
	        
			String getdiscussionCommentQuery = "SELECT * FROM picvik_tag WHERE " +
					"tag = '"+ tagname +"' ;";
			
			MyLog.log(getdiscussionCommentQuery);
			
			connection = com.picvik.util.MySqlConnection.getInstance().getConnection();
			
			preparedStatement = connection.prepareStatement(getdiscussionCommentQuery);
			resultSet = preparedStatement.executeQuery();
			
			
			// Tag already exists no need to insert again, return.
			if(resultSet.first()){
				resultSet.next();
				recentID= resultSet.getInt("tagid");
				 preparedStatement.close();
		         connection.close();
				return recentID;
			}
			
			// Do not already exists, insert it.
    		//query string 
    		String qstring = "INSERT INTO picvik_tag (tag) " +
    				"VALUES( '"+ tagname +  "' );";
    		
    		MyLog.log(qstring);
    		
    		connection = com.picvik.util.MySqlConnection.getInstance().getConnection();
            
    		preparedStatement = connection.prepareStatement(qstring);
            
            preparedStatement.executeUpdate(qstring);
            
            String getLastId = "SELECT LAST_INSERT_ID() FROM picvik_tag;";
            
            preparedStatement = connection.prepareStatement(getLastId);
            resultSet = preparedStatement.executeQuery();
            resultSet.next();
            recentID = resultSet.getInt(1);
            
            preparedStatement.close();
            connection.close();
		
    	} catch (Exception e) {
    		connection = null;
			preparedStatement = null;
			resultSet = null;
    		e.printStackTrace();
    	}		
		return recentID;
	}
	
	// Retrieval Methods
	
	// Returns a valid tag id if a tag with tagname exists, else returns a negative number.
	public int getTagIdWithTagName(String tagname){
		int tagid = -1;
		
		try{
			
			// Most recent comment at end. so ASC
			String getdiscussionCommentQuery = "SELECT * FROM picvik_tag WHERE " +
					"tag = '"+ tagname +"';";
			
			MyLog.log(getdiscussionCommentQuery);
			
			connection = com.picvik.util.MySqlConnection.getInstance().getConnection();
			
			preparedStatement = connection.prepareStatement(getdiscussionCommentQuery);
			resultSet = preparedStatement.executeQuery();
			
			while(resultSet.next()){
				tagid = resultSet.getInt("tagid");
			}	
			
			connection.close();
			preparedStatement.close();
			resultSet.close();
				
			}catch(Exception ex){
				connection = null;
				preparedStatement = null;
				resultSet = null;
				
				ex.printStackTrace();
			}

		return tagid;
	}
	
	// Returns a list of tag ids of a given list of tag names. Returns empty list if no tag is matched.
	public ArrayList<Integer> getTagIdsWithTagNames(ArrayList<String> tagnameList){
		ArrayList<Integer> tagIds = new ArrayList<Integer>();
				
		try{
			
			connection = com.picvik.util.MySqlConnection.getInstance().getConnection();
			
			for(String tagname : tagnameList){
								
				// Most recent comment at end. so ASC
				String getdiscussionCommentQuery = "SELECT * FROM picvik_tag WHERE " +
						"tag = '"+ tagname +"';";
				
				MyLog.log(getdiscussionCommentQuery);
				
				preparedStatement = connection.prepareStatement(getdiscussionCommentQuery);
				resultSet = preparedStatement.executeQuery();
				
				while(resultSet.next()){
					tagIds.add(resultSet.getInt("tagid"));
				}	
			}
			connection.close();
			preparedStatement.close();
			resultSet.close();
				
			}catch(Exception ex){
				connection = null;
				preparedStatement = null;
				resultSet = null;
				
				ex.printStackTrace();
			}
		
		return tagIds;
	}
	
	// Returns tag name of the given tagid, return empty string if no tag is found with that id.
	public String getTagName(int tagid){
		String tagName = "";
		
		try{			
			// Most recent comment at end. so ASC
			String getdiscussionCommentQuery = "SELECT * FROM picvik_tag WHERE " +
											   "tagid = "+ tagid +";";
				
				MyLog.log(getdiscussionCommentQuery);
				
				connection = com.picvik.util.MySqlConnection.getInstance().getConnection();
				
				preparedStatement = connection.prepareStatement(getdiscussionCommentQuery);
				resultSet = preparedStatement.executeQuery();
				
				while(resultSet.next()){
					tagName = resultSet.getString("tag");
				}	
			
			connection.close();
			preparedStatement.close();
			resultSet.close();
				
			}catch(Exception ex){
				connection = null;
				preparedStatement = null;
				resultSet = null;
				
				ex.printStackTrace();
			}

		return tagName;
	}
}